﻿'Description:人员信息维护类
'Copyright (c) : 通力凯顿（北京）系统集成有限公司
'Writer:zhunan
'create Date:2022.5.10
Imports DatabaseAccess.DBUtility
Imports BusinessRule.DTO
Imports System.Text
Imports External.APICalling
Imports System.Linq
Imports System.Data.OracleClient

Public Class EmployeeMaintPageBusiness

    ''' <summary>
    ''' 更新人员（日历、班次、能力）信息
    ''' </summary>
    ''' <param name="para"></param>
    ''' <param name="strMessage"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function UpdateEmployeeInfo(ByVal para As Dictionary(Of String, String), ByRef strMessage As String) As Boolean
        UpdateEmployeeInfo = False
        If Not para.ContainsKey("employeename") Then
            UpdateEmployeeInfo = False
            strMessage = "员工号不能为空！"
            Exit Function
        End If

        Dim dataEntityList As New List(Of ClientAPIEntity)
        Dim dataEntity As New ClientAPIEntity
        '员工号
        dataEntity = New ClientAPIEntity
        dataEntity.ClientDataName = "Name"
        dataEntity.ClientDataValue = para("employeename")
        dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
        dataEntityList.Add(dataEntity)

        '班次
        If para.ContainsKey("workshiftid") Then
            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataName = "WorkShiftID"
            dataEntity.ClientDataValue = para("workshiftid")
            dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
            dataEntityList.Add(dataEntity)
        End If

        '日历
        If para.ContainsKey("makecalendarid") Then
            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataName = "MakeCalendarID"
            dataEntity.ClientDataValue = para("makecalendarid")
            dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
            dataEntityList.Add(dataEntity)
        End If

        '能力
        If para.ContainsKey("skillnum") Then
            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataName = "skillNum"
            dataEntity.ClientDataValue = para("skillnum")
            dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
            dataEntityList.Add(dataEntity)
        End If

        '是否多机并行
        If para.ContainsKey("isParallel") Then
            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataName = "IsParallel"
            dataEntity.ClientDataValue = para("isParallel")
            dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
            dataEntityList.Add(dataEntity)
        End If

        '是否参与排产 add by zhangrj 2023-03-29
        If para.ContainsKey("isSchedule") Then
            dataEntity = New ClientAPIEntity
            dataEntity.ClientDataName = "isSchedule"
            dataEntity.ClientDataValue = para("isSchedule")
            dataEntity.ClientDataTypeEnum = DataTypeEnum.DataField
            dataEntityList.Add(dataEntity)
        End If

        Dim api As New CamstarClientAPI(para("userName"), para("passWord"))
        UpdateEmployeeInfo = api.UpdateModelByStandard("employeeMaintDoc", "employeeMaint", para("employeename"), "", dataEntityList, strMessage)
    End Function

    ''' <summary>
    ''' 更新人员关联的设备信息 add zhunan 2022.5.10
    ''' </summary>
    ''' <param name="para"></param>
    ''' <param name="dtResources"></param>
    ''' <param name="strMessage"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function SaveEmployeeResource(ByVal para As Dictionary(Of String, String), ByVal dtResources As DataTable, ByRef strMessage As String) As Boolean
        SaveEmployeeResource = False
        If Not para.ContainsKey("employeename") Then
            SaveEmployeeResource = False
            strMessage = "员工号不能为空！"
            Exit Function
        End If

        Dim DataEntityList As New List(Of ClientAPIEntity)
        DataEntityList.Add(New ClientAPIEntity("Name", InputTypeEnum.Details, DataTypeEnum.DataField, para("employeename"), ""))

        Dim dicResource As New Dictionary(Of Integer, String)
        For i As Integer = 0 To dtResources.Rows.Count - 1
            dicResource.Add(i, dtResources(i)("resourcename"))
        Next
        DataEntityList.Add(New ClientAPIEntity("ResourceList", InputTypeEnum.Details, DataTypeEnum.NamedObjectList, dicResource, "", para("resourceCount")))
        Dim api As New CamstarClientAPI(para("userName"), para("passWord"))
        SaveEmployeeResource = api.UpdateModelByStandard("employeeMaintDoc", "employeeMaint", para("employeename"), "", DataEntityList, strMessage)
    End Function

#Region "按班组维护（或导入）人员排班情况，用于滚动排产"

    ''' <summary>
    ''' 保存所选择的班次信息-包含插入和更新
    ''' </summary>
    ''' <param name="para"></param>
    ''' <remarks></remarks>
    Public Function SaveWorkShift(ByVal para As Dictionary(Of String, String)) As Boolean
        '判断人员-日期存在
        Dim sqlEmp As String = "select employeeid,fullname from employee e where e.employeename='{0}'"
        Dim dtEmp As DataTable = DatabaseHelper.Query_ReturnDatTable(String.Format(sqlEmp, para("employeename")))
        Dim employeeid As String = dtEmp.Rows(0)("employeeid").ToString()
        Dim fullname As String = dtEmp.Rows(0)("fullname").ToString()

        Dim sql As String = "select ae.workshiftid from apsemployeeworkdate ae where ae.employeeid='{0}'  and ae.workdate=to_date('{1}','yyyy-mm-dd') "
        sql = String.Format(sql, employeeid, para("workdate"))
        If DatabaseHelper.Query_ReturnDatTable(sql).Rows.Count > 0 Then
            If String.IsNullOrWhiteSpace(para("workshiftid")) Then
                sql = "delete ApsEmployeeWorkDate ae where employeeid='{0}' and to_char(ae.workdate,'yyyymmdd')='{1}' " '执行删除操作
                sql = String.Format(sql, employeeid, para("workdate"))
            Else
                sql = "update ApsEmployeeWorkDate ae set workshiftid='{0}' where employeeid='{1}' and to_char(ae.workdate,'yyyymmdd')='{2}' " '执行更新操作
                sql = String.Format(sql, para("workshiftid"), employeeid, para("workdate"))
            End If
            
        Else
            '如果点击的是空单元格，没有选择班次
            sql = " insert into apsemployeeworkdate(employeeid,workshiftid,fullname,workdate,apsemployeeworkdateid) values('{0}','{1}','{2}',to_date('{3}','yyyy-mm-dd'),'{4}')"
            sql = String.Format(sql, employeeid, para("workshiftid"), fullname, para("workdate"), System.Guid.NewGuid().ToString)
        End If
        SaveWorkShift = DatabaseHelper.ExecuteSql(sql)
    End Function

    ''' <summary>
    ''' 人员排班情况，数据查询
    ''' 根据导入的记录执行查询---》用户需要修改为没有导入时也能查询到记录
    ''' add:yuxj 20230328
    ''' </summary>
    ''' <param name="para"></param>
    ''' <param name="isPage">是否分页</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetEmpWkShiftSumInfoOld(ByVal para As Dictionary(Of String, String), Optional ByVal isPage As Boolean = False) As uMESProcessResult
        '获取日期子串 日期需要必选
        Dim result As New uMESProcessResult
        result.Result = False
        If Not para.ContainsKey("StartDate") Or (Not para.ContainsKey("EndDate")) Then
            result.Message = "请选择开始/结束日期"
            Return result
        End If

        Dim strDates As String = ""
        Dim startDate As Date = Date.Parse(para("StartDate"))
        While startDate <= Date.Parse(para("EndDate"))
            strDates = strDates + startDate.Date.ToString("yyyyMMdd") + ","
            startDate = startDate.AddDays(1)
        End While
        strDates = strDates.Trim(",")

        Dim strsql As String =
         <string>
                SELECT ae.fullname, e.employeename, to_char(ae.workdate,'yyyymmdd') workdate, ws.workshiftname
                FROM employee e
                left join ApsEmployeeWorkDate ae
                on e.employeeid = ae.employeeid  
                left join sessionvalues sv on sv.employeeid=e.employeeid
                left join teamemployee te on te.employeeid=e.employeeid
                left join team t on te.teamid=t.teamid
                left join workcenter wc on wc.workcenterid=t.workcenterid
                left join workshift ws  on ae.workshiftid = ws.workshiftid
                where ws.isuse=0 and e.IsSchedule='1'
            </string>
        strsql = GetEmpWkShiftSql(strsql, para)
        '行转列
        strsql = "select * from (" + strsql + ")" + "pivot(max(workshiftname) for workdate in (" + strDates + "))"

        If isPage Then '分页
            Dim paraPage As New uMESPagingDataDTO
            paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex"))
            paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
            paraPage.strSQL = strsql
            result = DatabaseHelper.RunProcedureForPage(paraPage)
        Else '不分页
            result.Result = True
            result.DBTable = DatabaseHelper.Query_ReturnDatTable(strsql)
        End If
        Return result
    End Function


    ''' <summary>
    ''' 人员排班情况，数据查询新方法
    ''' 用户排班没有导入时，按照人员查询
    ''' add:yuxj 20230328
    ''' </summary>
    ''' <param name="para"></param>
    ''' <param name="isPage">是否分页</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetEmpWkShiftSumInfo(ByVal para As Dictionary(Of String, String), Optional ByVal isPage As Boolean = False) As uMESProcessResult
        '获取日期子串 日期需要必选
        Dim result As New uMESProcessResult
        result.Result = False
        Try
            If Not para.ContainsKey("StartDate") Or (Not para.ContainsKey("EndDate")) Then
                result.Message = "请选择开始/结束日期"
                Return result
            End If

            Dim strsql As String =
             <string>
                SELECT e.fullname, e.employeename, to_char(ae.workdate,'yyyymmdd') workdate, ws.workshiftname
                FROM employee e
                left join ApsEmployeeWorkDate ae
                            on e.employeeid = ae.employeeid  and ae.workdate between to_date('{0}','yyyy-mm-dd') and to_date('{1}','yyyy-mm-dd')
                left join sessionvalues sv on sv.employeeid=e.employeeid
                left join teamemployee te on te.employeeid=e.employeeid
                left join team t on te.teamid=t.teamid
                left join workcenter wc on wc.workcenterid=t.workcenterid
                left join workshift ws  on ae.workshiftid = ws.workshiftid and ws.isuse=0 
                where e.IsSchedule='1'
            </string>
            strsql = String.Format(strsql, para("StartDate"), para("EndDate"))
            strsql = GetEmpWkShiftSql(strsql, para)
            ''行转列
            'strsql = "select * from (" + strsql + ")" + "pivot(max(workshiftname) for workdate in (" + strDates + "))"
            Dim dt As DataTable = DatabaseHelper.Query_ReturnDatTable(strsql) '查询到的人员排班记录
            If dt.Rows.Count = 0 Then
                result.Message = "没有人员需要排班"
                result.DBTable = New DataTable
                Return result
            End If

            '获取人员
            Dim dtEmp As DataTable = dt.DefaultView.ToTable(True, "employeename", "fullname")
            Dim strDates As String = ""
            Dim startDate As Date = Date.Parse(para("StartDate"))

            '添加列
            While startDate <= Date.Parse(para("EndDate"))
                dtEmp.Columns.Add(startDate.Date.ToString("yyyyMMdd"))
                startDate = startDate.AddDays(1)
            End While

            '填充数据
            Dim drs As DataRow()
            For Each col As DataColumn In dtEmp.Columns
                If col.ColumnName.ToLower = "employeename" Or col.ColumnName.ToLower = "fullname" Then
                    Continue For
                End If
                For Each drEmp As DataRow In dtEmp.Rows
                    drs = dt.Select(String.Format("employeename='{0}' and workdate='{1}' ", drEmp("employeename"), col.ColumnName))
                    If drs.Length > 0 Then
                        drEmp(col.ColumnName) = drs(0)("workshiftname")
                    End If
                Next
            Next
            result.Result = True
            result.DBTable = dtEmp
            Return result
        Catch ex As Exception
            result.Message = "获取数据错误:" + ex.Message
            result.DBTable = New DataTable
            Return result
        End Try
        
        
    End Function


    ''' <summary>
    ''' 人员排班情况，导出数据查询
    ''' add:yuxj 20230328
    ''' </summary>
    ''' <param name="para"></param>
    ''' <param name="isPage">是否分页</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetEmpWkShiftDetail(ByVal para As Dictionary(Of String, String), Optional ByVal isPage As Boolean = False) As uMESProcessResult
        '获取日期子串 日期需要必选
        Dim result As New uMESProcessResult
        result.Result = False
        Dim strsql As String =
          <string>
                SELECT ae.fullname, e.employeename,ws.workshiftname, to_char(ae.workdate,'yyyy-mm-dd') workdate, 
                       wst.startdate,wst.enddate,wst.mins
                FROM ApsEmployeeWorkDate ae
                left join employee e
                on e.employeeid = ae.employeeid  
                left join sessionvalues sv on sv.employeeid=e.employeeid
                left join teamemployee te on te.employeeid=e.employeeid
                left join team t on te.teamid=t.teamid
                left join workcenter wc on wc.workcenterid=t.workcenterid
                left join workshift ws  on ae.workshiftid = ws.workshiftid
                left join workshiftdetail wst on wst.workshiftid=ws.workshiftid
                where ws.isuse=0 and e.IsSchedule='1'
            </string>
        strsql = strsql + String.Format(" and ae.workdate >=to_date('{0}','yyyy-mm-dd') ", para("StartDate"))
        strsql = strsql + String.Format(" and ae.workdate <=to_date('{0}','yyyy-mm-dd') ", para("EndDate"))
        strsql = GetEmpWkShiftSql(strsql, para)
        strsql = strsql + " order by e.employeename,ae.workdate,ws.workshiftname asc"
        If isPage Then '分页
            Dim paraPage As New uMESPagingDataDTO
            paraPage.CurrentPageIndex = Convert.ToInt32(para("CurrentPageIndex"))
            paraPage.PageSize = Convert.ToInt32(para("PageSize").ToString)
            paraPage.strSQL = strsql
            result = DatabaseHelper.RunProcedureForPage(paraPage)
        Else '不分页
            result.Result = True
            result.DBTable = DatabaseHelper.Query_ReturnDatTable(strsql)
        End If
        Return result
    End Function

    ''' <summary>
    ''' 获取人员排班情况sql yuxj20230329
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function GetEmpWkShiftSql(ByRef strsql As String, ByVal para As Dictionary(Of String, String)) As String
        If para.ContainsKey("FactoryId") Then
            strsql = strsql + String.Format(" and sv.FactoryId ='{0}' ", para("FactoryId"))
        End If

        If para.ContainsKey("WorkCenterId") Then
            strsql = strsql + String.Format(" and wc.WorkCenterId ='{0}' ", para("WorkCenterId"))
        End If

        If para.ContainsKey("TeamId") Then
            strsql = strsql + String.Format(" and t.TeamId ='{0}' ", para("TeamId"))
        End If

        If para.ContainsKey("EmployeeName") Then
            strsql = strsql + String.Format(" and e.EmployeeName like '%{0}%' ", para("EmployeeName"))
        End If

        If para.ContainsKey("FullName") Then
            strsql = strsql + String.Format(" and e.FullName like '%{0}%' ", para("FullName"))
        End If
        Return strsql
    End Function

    ''' <summary>
    ''' 排班数据导入yuxj20230329
    ''' </summary>
    ''' <param name="empWKShiftDt"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function EmployeeWorkDateImport(ByVal empWKShiftDt As DataTable) As Boolean
        Try
            Dim workshiftid As String = ""
            Dim workdate As String = ""
            Dim fullname As String = ""
            Dim employeeid As String = ""
            '判断人员-日期存在
            Dim sqlWKExist As String = "select ae.workshiftid from apsemployeeworkdate ae where ae.employeeid='{0}'  and ae.workdate=to_date('{1}','yyyy-mm-dd') "
            '插入语句
            Dim sqlInsert As String = " insert into apsemployeeworkdate(employeeid,workshiftid,fullname,workdate,apsemployeeworkdateid) values('{0}','{1}','{2}',to_date('{3}','yyyy-mm-dd'),'{4}')"
            '更新语句
            Dim sqlUpdate As String = " update apsemployeeworkdate set workshiftid='{0}' where employeeid='{1}' and workdate=to_date('{2}','yyyy-mm-dd')"

            '删除语句
            Dim sqlDelete As String = " delete apsemployeeworkdate where employeeid='{0}' and workdate=to_date('{1}','yyyy-mm-dd')"

            Dim sqlList As New ArrayList
            Dim dtTemp As New DataTable

            For Each dr In empWKShiftDt.Rows
                employeeid = dr("employeeid").ToString
                workshiftid = dr("workshiftid").ToString
                workdate = dr("workdate").ToString
                fullname = dr("fullname").ToString

                dtTemp = DatabaseHelper.Query_ReturnDatTable(String.Format(sqlWKExist, employeeid, workdate))
                If dtTemp.Rows.Count > 0 Then
                    '判断人员-日期存在执行更新
                    If dtTemp.Rows(0)("workshiftid").ToString = workshiftid Then
                        '完全相同，不用执行
                        Continue For
                    ElseIf String.IsNullOrWhiteSpace(dtTemp.Rows(0)("workshiftid").ToString) Then '删除
                        sqlList.Add(String.Format(sqlDelete, employeeid, workdate))
                    Else '更新
                        sqlList.Add(String.Format(sqlUpdate, workshiftid, employeeid, workdate))
                    End If
                Else '插入
                    sqlList.Add(String.Format(sqlInsert, employeeid, workshiftid, fullname, workdate, System.Guid.NewGuid().ToString))
                End If
            Next
            DatabaseHelper.ExecuteSqlTran(sqlList)
            Return True
        Catch ex As Exception
            Return False
        End Try
    End Function

#End Region
End Class
